

import os
import xlwings as xw

def process_excel_files(folder_path):
    """处理文件夹内所有Excel文件"""
    app = xw.App(visible=False)
    try:
        for filename in os.listdir(folder_path):
            if filename.lower().endswith(('.xlsx', '.xls', '.xlsm')):
                file_path = os.path.join(folder_path, filename)
                process_single_file(app, file_path)
    finally:
        app.quit()

def process_single_file(app, file_path):
    """处理单个Excel文件"""
    try:
        wb = app.books.open(file_path)
        for sheet in wb.sheets:
            process_worksheet(sheet)
        
        base, ext = os.path.splitext(file_path)
        output_path = f"{base}_修改精度{ext}"
        wb.save(output_path)
        print(f"处理完成: {file_path} -> {output_path}")
    except Exception as e:
        print(f"处理 {file_path} 时出错: {str(e)}")
    finally:
        wb.close()

def process_worksheet(sheet):
    """处理整个工作表数据"""
    # 获取整个工作表的使用范围
    used_range = sheet.used_range
    
    # 查找单位和数量列
    unit_col = None
    qty_col = None
    qty_cols = []
    
    # 从前10行查找列标题
    search_range = sheet.range('A2:Z10')
    
    # 在前10行中查找列标题
    for row in search_range:
        for cell in row:
            if cell.value and str(cell.value).strip() == "Unit\n单位":
                unit_col = cell.column
            elif cell.value and str(cell.value).strip() == "Qty\n数量":
                qty_cols.append(cell.column)
        
    
    if not unit_col or not qty_cols:
        print("警告：未在前10行中找到'单位'或'数量'列")
        return
    
    # 处理整个数据区域，存在多个数量列，循环遍历修改
    for qty_col in qty_cols:
        data_range = sheet.range((2, unit_col), (used_range.rows.count, qty_col))
        # print(data_range)
        for cell in data_range:
            if not cell.value:
                continue
                
            unit = str(cell.value).lower()
            qty_cell = sheet.range(cell.row, qty_col)
            
            try:
                if 'kg' in unit:
                    qty_cell.value = round(float(qty_cell.value))
                    qty_cell.number_format = '0'
                elif any(m in unit for m in ['m', 'm2', 'm3']):
                    qty_cell.value = round(float(qty_cell.value), 1)
                    qty_cell.number_format = '0.0'
            except (ValueError, TypeError):
                continue

if __name__ == "__main__":
    folder = input("请输入Excel文件所在文件夹路径: ")
    process_excel_files(folder)
